package com.demo2do.fms.admin.entity.report.business;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.WeakHashMap;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 * 查询派车单/维护派车单
 *
 * @author Ark
 *
 */
public class DispatchOrderReportSearch implements QueryBuilder {

    /** 所选择公司 */
    private List<Long> organizationIds;
    
    /** 派车单号 */
    private String dispatchOrderCode;
    
    /** 驳运车号 */
    private Long vehicleId;
    
    /** 安吉调度指令组号 */
    private String shipmentGroupId;
    
    /** 起始地 装卸地 */
    private Long startFacilityId;
    
    /** 目的地 装卸地 */
    private Long destinationFacilityId;
    
    /** 派车单日期  */
    private String beginDispatchDate;
    
    /** 派车单日期 */
    private String endDispatchDate;
    
    /** 报销开始日期 - 查询派车单用  */
    private String beginExpenseReimburseDate;
    
    /** 报销结束日期 - 查询派车单用  */
    private String endExpenseReimburseDate;
    
    /** 预计出发最早日期 - 维护派车单用 */
    private String beginLeavingDate;
    
    /** 预计出发最晚日期 - 维护派车单用  */
    private String endLeavingDate;
    
    /** 主驾驶员 */
    private Long mainDriverId;
    
    /** 第一副驾驶员 */
    private Long firstViceDriverId;
    
    /** 
     * 是否已报销
     *  
     *      - 查询派车单， 选择框
     *      - 维护派车单， IS_EXPENSE_REIMBURSED = 0
     */
    private Boolean expenseReimbursed;
    
    // ========= 排序 ===========
    /** 派车单号 */
    private String codeSort;
    
    /** 派车时间 */
    private String dispatchDateSort;
    
    /** 调度指令组好 */
    private String shipmentGroupIdSort;
    
    private QueryOrder order;
    
    private QueryFilter filter;
    
    // ======== 查询类别 ============
    private String target;
    
    /** 导出条数上限 */
    private Integer limit;
    
    /**
     * The default constructor
     */
    public DispatchOrderReportSearch() {
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (filter == null || refresh) {
            
            filter = new QueryFilter().addEquals("a", "dispatch_order_code", dispatchOrderCode)
                                      .addEquals("a", "vehicle_id", vehicleId)
                                      .addEquals("a", "shipment_group_id", shipmentGroupId)
                                      .addEquals("b", "id", startFacilityId)
                                      .addEquals("c", "id", destinationFacilityId);
                                      
            if (StringUtils.isNotBlank(beginDispatchDate)) {
                filter.addSentence("a.dispatch_date >= TO_DATE('" + beginDispatchDate.replace("-", "") + "000000', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endDispatchDate)) {
                filter.addSentence("a.dispatch_date <= TO_DATE('" + endDispatchDate.replace("-", "") + "235959', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(beginExpenseReimburseDate)) {
                filter.addSentence("a.expense_reimburse_date >= TO_DATE('" + beginExpenseReimburseDate.replace("-", "") + "000000', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endExpenseReimburseDate)) {
                filter.addSentence("a.expense_reimburse_date <= TO_DATE('" + endExpenseReimburseDate.replace("-", "") + "235959', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(beginLeavingDate)) {
                filter.addSentence("a.leaving_time >= TO_DATE('" + beginLeavingDate.replace("-", "") + "000000', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endLeavingDate)) {
                filter.addSentence("a.leaving_time <= TO_DATE('" + endLeavingDate.replace("-", "") + "235959', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            filter.addEquals("e", "id", mainDriverId)
                  .addEquals("f", "id", firstViceDriverId)
                  .addEquals("a", "is_expense_reimbursed", expenseReimbursed)
                  .addSentence("a.organization_id IN (:organizationIds)", "organizationIds", organizationIds)
                  .addLessThanEqual("ROWNUM", limit);
                                      
        }
        
        return filter;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(512);
        
        buffer.append("SELECT o.organization_name AS organization, ")
                     .append("a.dispatch_order_code AS code, ")
                     .append("a.leaving_time AS leavingTime, ")
                     .append("a.dispatch_date AS dispatchDate, ")
                     .append("a.shipment_group_id AS shipmentGroupId, ")
                     .append("b.facility_code || ' - ' || b.facility_name AS startFacility, ")
                     .append("c.facility_code || ' - ' || c.facility_name AS destinationFacility, ")
                     .append("d.vehicle_code || ' - ' || d.vehicle_name AS vehicle, ")
                     .append("e.name AS mainDriver, ")
                     .append("f.name AS firstViceDriver, ")
                     .append("a.actual_load_number AS actualLoadNumber, ")
                     .append("a.accomplish_confirm_number AS accomplishConfirmNumber, ")
                     .append("a.is_expense_reimbursed AS expenseReimbursed ")
              .append("FROM tbl_truck_dispatch_order a, vw_load_place b, vw_load_place c, vw_vehicle d, tbl_all_employee_info e, tbl_all_employee_info f, vw_transportation_company o ")
              .append("WHERE a.is_disabled=0 AND a.start_facility_id=b.id(+) AND a.destination_facility_id=c.id(+) AND a.vehicle_id=d.id(+) AND a.main_driver_id=e.id (+) AND a.first_vice_driver_id=f.id (+) AND a.organization_id = o.id") 
              .append(getPreparedFilter(false).clauseSentence() + getPreparedOrder(false).sentence());
        
        return buffer.toString();
        
    }
    
    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {

            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("organization", rs.getObject("organization"));
                result.put("code", rs.getObject("code"));
                result.put("leavingTime", rs.getObject("leavingTime"));
                result.put("dispatchDate", rs.getObject("dispatchDate"));
                result.put("shipmentGroupId", rs.getObject("shipmentGroupId"));
                result.put("startFacility", rs.getObject("startFacility"));
                result.put("destinationFacility", rs.getObject("destinationFacility"));
                result.put("vehicle", rs.getObject("vehicle"));
                result.put("mainDriver", rs.getObject("mainDriver"));
                result.put("firstViceDriver", rs.getObject("firstViceDriver"));
                result.put("actualLoadNumber", rs.getObject("actualLoadNumber"));
                result.put("accomplishConfirmNumber", rs.getObject("accomplishConfirmNumber"));
                result.put("expenseReimbursed", rs.getBoolean("expenseReimbursed"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return order;
    }
    
    /**
     * @param refresh
     * @return
     */
    public QueryOrder getPreparedOrder(boolean refresh) {
        
        if (order == null || refresh) {
            
            order = new QueryOrder();
            
            if (StringUtils.isNotBlank(codeSort)) {
                
                order.add("a", "dispatch_order_code", codeSort);
                
            } else if (StringUtils.isNotBlank(dispatchDateSort)) {
                
                order.add("a", "dispatch_date", dispatchDateSort);
                
            } else if (StringUtils.isNotBlank(shipmentGroupIdSort)) {
                
                order.add("a", "shipment_group_id", shipmentGroupIdSort);
                
            } else {
                
                if (StringUtils.equals("maintain", target)) {
                    order.add("a", "leaving_time", "DESC");
                }
                
            }
            
        }
        
        return order;
        
    }
    
    /*
     * (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /**
     * @return
     */
    public QueryFilter getFilter(){
        return getPreparedFilter(false);
    }

    /**
     * @return the organizationIds
     */
    public List<Long> getOrganizationIds() {
        return organizationIds;
    }

    /**
     * @return the dispatchOrderCode
     */
    public String getDispatchOrderCode() {
        return dispatchOrderCode;
    }

    /**
     * @return the vehicleId
     */
    public Long getVehicleId() {
        return vehicleId;
    }

    /**
     * @return the shipmentGroupId
     */
    public String getShipmentGroupId() {
        return shipmentGroupId;
    }

    /**
     * @return the startFacilityId
     */
    public Long getStartFacilityId() {
        return startFacilityId;
    }

    /**
     * @return the destinationFacilityId
     */
    public Long getDestinationFacilityId() {
        return destinationFacilityId;
    }

    /**
     * @return the beginDispatchDate
     */
    public String getBeginDispatchDate() {
        return beginDispatchDate;
    }

    /**
     * @return the endDispatchDate
     */
    public String getEndDispatchDate() {
        return endDispatchDate;
    }

    /**
     * @return the beginExpenseReimburseDate
     */
    public String getBeginExpenseReimburseDate() {
        return beginExpenseReimburseDate;
    }

    /**
     * @return the endExpenseReimburseDate
     */
    public String getEndExpenseReimburseDate() {
        return endExpenseReimburseDate;
    }

    /**
     * @return the beginLeavingDate
     */
    public String getBeginLeavingDate() {
        return beginLeavingDate;
    }

    /**
     * @return the endLeavingDate
     */
    public String getEndLeavingDate() {
        return endLeavingDate;
    }

    /**
     * @return the mainDriverId
     */
    public Long getMainDriverId() {
        return mainDriverId;
    }

    /**
     * @return the firstViceDriverId
     */
    public Long getFirstViceDriverId() {
        return firstViceDriverId;
    }

    /**
     * @return the expenseReimbursed
     */
    public Boolean getExpenseReimbursed() {
        return expenseReimbursed;
    }
    
    /**
     * @return the codeSort
     */
    public String getCodeSort() {
        return codeSort;
    }

    /**
     * @return the dispatchDateSort
     */
    public String getDispatchDateSort() {
        return dispatchDateSort;
    }

    /**
     * @return the shipmentGroupIdSort
     */
    public String getShipmentGroupIdSort() {
        return shipmentGroupIdSort;
    }
    
    /**
     * @return
     */
    public String getTarget() {
        return target;
    }
    
    /**
     * @return
     */
    public Integer getLimit() {
        return limit;
    }

    /**
     * @param organizationIds the organizationIds to set
     */
    public void setOrganizationIds(List<Long> organizationIds) {
        this.organizationIds = organizationIds;
    }

    /**
     * @param dispatchOrderCode the dispatchOrderCode to set
     */
    public void setDispatchOrderCode(String dispatchOrderCode) {
        this.dispatchOrderCode = dispatchOrderCode;
    }

    /**
     * @param vehicleId the vehicleId to set
     */
    public void setVehicleId(Long vehicleId) {
        this.vehicleId = vehicleId;
    }

    /**
     * @param shipmentGroupId the shipmentGroupId to set
     */
    public void setShipmentGroupId(String shipmentGroupId) {
        this.shipmentGroupId = shipmentGroupId;
    }

    /**
     * @param startFacilityId the startFacilityId to set
     */
    public void setStartFacilityId(Long startFacilityId) {
        this.startFacilityId = startFacilityId;
    }

    /**
     * @param destinationFacilityId the destinationFacilityId to set
     */
    public void setDestinationFacilityId(Long destinationFacilityId) {
        this.destinationFacilityId = destinationFacilityId;
    }

    /**
     * @param beginDispatchDate the beginDispatchDate to set
     */
    public void setBeginDispatchDate(String beginDispatchDate) {
        this.beginDispatchDate = beginDispatchDate;
    }

    /**
     * @param endDispatchDate the endDispatchDate to set
     */
    public void setEndDispatchDate(String endDispatchDate) {
        this.endDispatchDate = endDispatchDate;
    }

    /**
     * @param beginExpenseReimburseDate the beginExpenseReimburseDate to set
     */
    public void setBeginExpenseReimburseDate(String beginExpenseReimburseDate) {
        this.beginExpenseReimburseDate = beginExpenseReimburseDate;
    }

    /**
     * @param endExpenseReimburseDate the endExpenseReimburseDate to set
     */
    public void setEndExpenseReimburseDate(String endExpenseReimburseDate) {
        this.endExpenseReimburseDate = endExpenseReimburseDate;
    }

    /**
     * @param beginLeavingDate the beginLeavingDate to set
     */
    public void setBeginLeavingDate(String beginLeavingDate) {
        this.beginLeavingDate = beginLeavingDate;
    }

    /**
     * @param endLeavingDate the endLeavingDate to set
     */
    public void setEndLeavingDate(String endLeavingDate) {
        this.endLeavingDate = endLeavingDate;
    }

    /**
     * @param mainDriverId the mainDriverId to set
     */
    public void setMainDriverId(Long mainDriverId) {
        this.mainDriverId = mainDriverId;
    }

    /**
     * @param firstViceDriverId the firstViceDriverId to set
     */
    public void setFirstViceDriverId(Long firstViceDriverId) {
        this.firstViceDriverId = firstViceDriverId;
    }

    /**
     * @param expenseReimbursed the expenseReimbursed to set
     */
    public void setExpenseReimbursed(Boolean expenseReimbursed) {
        this.expenseReimbursed = expenseReimbursed;
    }

    /**
     * @param codeSort the codeSort to set
     */
    public void setCodeSort(String codeSort) {
        this.codeSort = codeSort;
    }

    /**
     * @param dispatchDateSort the dispatchDateSort to set
     */
    public void setDispatchDateSort(String dispatchDateSort) {
        this.dispatchDateSort = dispatchDateSort;
    }

    /**
     * @param shipmentGroupIdSort the shipmentGroupIdSort to set
     */
    public void setShipmentGroupIdSort(String shipmentGroupIdSort) {
        this.shipmentGroupIdSort = shipmentGroupIdSort;
    }

    /**
     * @param target
     */
    public void setTarget(String target) {
        this.target = target;
    }
    
    /**
     * @param limit
     */
    public void setLimit(Integer limit) {
        this.limit = limit;
    }
    
}
